using System;
using System.Collections.Generic;
using System.Text;
using CrudI.Interface;

namespace CrudCreator.Creators
{
    public class CreateSqlUpdateProcedure : AbsSqlProcedure
    {
        private CreateSqlUpdateProcedure(string procedurePrefix)
            : base("Update", procedurePrefix)
        {
            sbProcedure = new StringBuilder();
        }

        /// <summary>
        /// Gets the update procedure for table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <param name="procedurePrefix">The procedure prefix.</param>
        /// <returns></returns>
        public static string GetUpdateProcedure(ITable table, string databaseName, string procedurePrefix)
        {
            CreateSqlUpdateProcedure updateObject = new CreateSqlUpdateProcedure(procedurePrefix);

            updateObject.sbProcedure.Append(updateObject.procedureBegining + " ");
            updateObject.sbProcedure.AppendLine(databaseName + "." + table.TableName + updateObject.ProcedureType + " "  + Environment.NewLine);

            updateObject.sbProcedure.AppendLine("CREATE PROCEDURE " + databaseName + "." + table.TableName + updateObject.ProcedureType + Environment.NewLine);

            //insertProcedureObject.sbProcedure.AppendLine(insertProcedureObject.procedureBegining);
            updateObject.AddParameters(table);
            updateObject.BuildUpdate(table, databaseName);
            updateObject.procedureEnd = Environment.NewLine + " END" + Environment.NewLine;
            updateObject.sbProcedure.Append(updateObject.procedureEnd);

            return updateObject.sbProcedure.ToString();
        }

        private void AddParameters(ITable table)
        {
            sbProcedure.AppendLine("(");
            List<IColumn> cols = table.ColumnList;
            for (int i = 0; i < cols.Count; i++)
            {                
                if ((i + 1) < cols.Count)
                  sbProcedure.AppendLine("\tp" + cols[i].ColumnName + " " + cols[i].DataType + ", ");
                else sbProcedure.AppendLine("\tp" + cols[i].ColumnName + " " + cols[i].DataType);
            }

            sbProcedure.AppendLine(Environment.NewLine + ")");
            sbProcedure.AppendLine("BEGIN" + Environment.NewLine); 
        }

        private void BuildUpdate(ITable table, string databaseName)
        {
            string update = "\t" + ProcedureType + " " + databaseName + "." + table.TableName + " SET ";
            sbProcedure.Append(update);
            string whereClause = string.Empty;
            int paramsOnALineCounter = 1;

            for (int i = 0; i < table.ColumnList.Count; i++)
            {
                if (!table.ColumnList[i].IsPK)
                {
                    if ((i + 1) < table.ColumnList.Count)
                    { // we are not at the last parameter

                        if (paramsOnALineCounter % 4 == 0) // put four on a line (field = param)
                        {
                            sbProcedure.Append(Environment.NewLine + "\t  " + table.ColumnList[i].ColumnName + " = p" + table.ColumnList[i].ColumnName + ", ");
                            paramsOnALineCounter = 1;
                        }
                        else
                        {
                            paramsOnALineCounter++;
                            sbProcedure.Append(table.ColumnList[i].ColumnName + " = p" + table.ColumnList[i].ColumnName + ", ");
                        }
                    }
                    else
                    {   // last parameter so NO apastrophe abd we dont need to increment paramsOnALineCounter var
                        if (paramsOnALineCounter % 4 == 0) // put four on a line (field = param)
                            sbProcedure.Append(Environment.NewLine + "\t " + table.ColumnList[i].ColumnName + " = p" + table.ColumnList[i].ColumnName);
                        else
                            sbProcedure.Append(table.ColumnList[i].ColumnName + " = p" + table.ColumnList[i].ColumnName);
                    }
                }
                else
                {
                    // primary key column so build where clause
                    whereClause = Environment.NewLine + "\tWHERE " + table.ColumnList[i].ColumnName + " = p" + table.ColumnList[i].ColumnName + ";";
                }

            }
            sbProcedure.AppendLine(whereClause);
           
        }
    }
}
